import sqlite3
from datetime import datetime

class GameDatabase:
    def __init__(self):
        """初始化数据库连接"""
        self.conn = sqlite3.connect('game_history.db')
        self.create_tables()
    
    def create_tables(self):
        """创建必要的数据表"""
        cursor = self.conn.cursor()
        
        # 创建游戏记录表
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS games (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                timestamp TEXT NOT NULL,
                final_mood INTEGER NOT NULL,
                result TEXT NOT NULL
            )
        ''')
        
        # 创建互动记录表
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS interactions (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                game_id INTEGER NOT NULL,
                action TEXT NOT NULL,
                response TEXT NOT NULL,
                mood_change INTEGER NOT NULL,
                FOREIGN KEY (game_id) REFERENCES games (id)
            )
        ''')
        
        self.conn.commit()
    
    def start_new_game(self):
        """开始新游戏，返回游戏ID"""
        cursor = self.conn.cursor()
        cursor.execute(
            'INSERT INTO games (timestamp, final_mood, result) VALUES (?, ?, ?)',
            (datetime.now().strftime('%Y-%m-%d %H:%M:%S'), 50, '进行中')
        )
        self.conn.commit()
        return cursor.lastrowid
    
    def add_interaction(self, game_id, action, response, mood_change):
        """记录一次互动"""
        cursor = self.conn.cursor()
        cursor.execute(
            'INSERT INTO interactions (game_id, action, response, mood_change) VALUES (?, ?, ?, ?)',
            (game_id, action, response, mood_change)
        )
        self.conn.commit()
    
    def end_game(self, game_id, final_mood, result):
        """结束游戏，更新结果"""
        cursor = self.conn.cursor()
        cursor.execute(
            'UPDATE games SET final_mood = ?, result = ? WHERE id = ?',
            (final_mood, result, game_id)
        )
        self.conn.commit()
    
    def get_all_games(self):
        """获取所有游戏记录"""
        cursor = self.conn.cursor()
        cursor.execute('SELECT * FROM games ORDER BY timestamp DESC')
        return cursor.fetchall()
    
    def get_session_interactions(self, game_id):
        """获取指定游戏的所有互动记录"""
        cursor = self.conn.cursor()
        cursor.execute(
            'SELECT * FROM interactions WHERE game_id = ? ORDER BY id',
            (game_id,)
        )
        return cursor.fetchall()
    
    def __del__(self):
        """关闭数据库连接"""
        self.conn.close()